package br.com.dao;

import br.com.classes.Banco;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Eziquiel
 */
public class BancoDao extends Conecta {

    Connection con = null;

    public BancoDao() {
        con = Conecta.getConnection();
    }

    public void CadastrarBanco(Banco b) throws Exception {
        String sql = ("insert into banco values(null,?,?,?)");
        PreparedStatement prep = con.prepareStatement(sql);

        prep.setString(1, b.getBanco());
        prep.setString(2, b.getAgencia());
        prep.setString(3, b.getConta());
        prep.execute();
    }

    public void AlterarBanco(Banco b) throws Exception {
        try {

            String sql = ("update banco SET banco = ?, agencia =?, conta =? WHERE cod_banco =?");
            PreparedStatement ps = con.prepareStatement(sql);

            ps.setString(1, b.getBanco());
            ps.setString(2, b.getAgencia());
            ps.setString(3, b.getConta());
            ps.setInt(4, b.getCod_banco());
            ps.execute();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public void DeletarBanco(Banco b) throws Exception {

        String sql = ("delete from banco where banco = ?");
        PreparedStatement prep = con.prepareStatement(sql);
        prep.setString(1, b.getBanco());
        prep.execute();
    }

    public List<Banco> ListarBanco() {

        List<Banco> lista = new ArrayList<Banco>();
        try {
            String sql = "select * from banco";
            PreparedStatement stmt = con.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Banco ban = new Banco();
                ban.setCod_banco(rs.getInt("cod_banco"));
                ban.setBanco(rs.getString("banco"));
                ban.setAgencia(rs.getString("agencia"));
                ban.setConta(rs.getString("conta"));
                lista.add(ban);
            }
        } catch (SQLException ex) {
        }
        return lista;
    }

    public int UltimoCodBanco() {

        int cod = 0;
        try {

            String sql = "select max(cod_banco) + 1 as codigo from banco";
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                cod = rs.getInt("codigo");
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return cod;
    }

    public List<Banco> BuscarLimite(int num) {

        List<Banco> lista = new ArrayList<Banco>();
        try {

            String sql = "select * from banco limit ?,200";
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setInt(1, num);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Banco b = new Banco();
                b.setCod_banco(rs.getInt("cod_banco"));
                b.setBanco(rs.getString("banco"));
                b.setAgencia(rs.getString("agencia"));
                b.setConta(rs.getString("conta"));
                lista.add(b);
            }
        } catch (SQLException ex) {
        }
        return lista;
    }

    public List<Banco> ProcurarPorBancoLetra(String banco) {

        List<Banco> lista = new ArrayList<Banco>();
        try {

            String sql = "select * from banco where banco like ?";
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setString(1, "%" + banco + "%");
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {

                Banco b = new Banco();
                b.setCod_banco(rs.getInt("cod_banco"));
                b.setBanco(rs.getString("banco"));
                b.setAgencia(rs.getString("agencia"));
                b.setConta(rs.getString("conta"));
                lista.add(b);
            }

        } catch (SQLException ex) {
        }
        return lista;
    }

    public int PesquisarBancoMov(int cod_banco) {

        int compara = 0;

        try {
            String sql = "SELECT cod_banco FROM  banco where cod_banco = ? ";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, cod_banco);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                if (rs.equals(0)) {
                    compara = 0;
                } else {
                    compara = 1;
                }
            }
        } catch (SQLException d) {
            System.out.println(d.getMessage());
        }
        return compara;
    }
}
